﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.OracleClient;
using TracNghiem.Models;
using System.Configuration;
using System.Data;

namespace TracNghiem.Areas.Admin.Models
{
    public class Noi_DungModels
    {
        public string id { get; set; }
        public string ID_BAIVIET { get; set; }
        public string ID_PHANLOAI { get; set; }
        public string NOIDUNG { get; set; }

        public string tendichvu { get; set; }
        public string tenphanloai { get; set; }
        public SelectList service_id { get; set; }
        public List<Noi_DungModels> GETNOIDUNG(int ID_BAIVIET)
        {
            List<Noi_DungModels> ND = new List<Noi_DungModels>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT     NOIDUNG.ID, NOIDUNG.ID_BAIVIET, NOIDUNG.NOIDUNG, PHANLOAI.NAME, NOIDUNG.ID_PHANLOAI FROM PHANLOAI INNER JOIN NOIDUNG ON PHANLOAI.ID = NOIDUNG.ID_PHANLOAI WHERE noidung.id_baiviet=" + ID_BAIVIET + "ORDER BY phanloai.vi_tri ASC");

            while (odr.Read())
            {

                ND.Add(new Noi_DungModels
                {
                    id = odr["id"].ToString(),
                    ID_BAIVIET = odr["ID_BAIVIET"].ToString(),
                    ID_PHANLOAI = odr["ID_PHANLOAI"].ToString(),
                    NOIDUNG = odr["NOIDUNG"].ToString(),
                    tenphanloai = odr["name"].ToString(),

                });
            }
            General.conn_global.Close();
            return ND;
        }
        public void addNoiDung(Noi_DungModels ND)
        {

            string str = ConfigurationManager.ConnectionStrings["tracnghiem"].ConnectionString;
            OracleConnection conn = new OracleConnection(str);
            OracleCommand cmd = new OracleCommand("THEMNOIDUNG", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("ID_BAIVIET", OracleType.Number).Value = ND.ID_BAIVIET;
            cmd.Parameters.Add("ID_PHANLOAI", OracleType.Number).Value = ND.ID_PHANLOAI;
            cmd.Parameters.Add("NOIDUNG", OracleType.Clob).Value = ND.NOIDUNG;
         
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();


            //string sql = "INSERT INTO NOIDUNG";
            //sql += " select nvl((select max(id)+1 from NOIDUNG),1)," + ND.ID_BAIVIET + "," + ND.ID_PHANLOAI+ ",'"+ND.NOIDUNG+"' from dual";
            //General.Lib_ExecuteNonQuery(sql);
        }
        public void update(Noi_DungModels ND)
        {



            string str = ConfigurationManager.ConnectionStrings["tracnghiem"].ConnectionString;
            OracleConnection conn = new OracleConnection(str);
            OracleCommand cmd = new OracleCommand("editnoidung", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("ID_", OracleType.Number).Value = ND.id;
            cmd.Parameters.Add("ID_PHANLOAI_", OracleType.Number).Value = ND.ID_PHANLOAI;
            cmd.Parameters.Add("NOIDUNG_", OracleType.Clob).Value = ND.NOIDUNG;

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

        }
     

        internal void deletenoidung(int id)
        {
            string sql = "DELETE FROM noidung WHERE id=" + id + "";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }

        public List<Noi_DungModels> GETNOIDUNG()
        {
            List<Noi_DungModels> ND = new List<Noi_DungModels>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT     NOIDUNG.ID, NOIDUNG.ID_BAIVIET, NOIDUNG.NOIDUNG, PHANLOAI.NAME, NOIDUNG.ID_PHANLOAI FROM PHANLOAI INNER JOIN NOIDUNG ON PHANLOAI.ID = NOIDUNG.ID_PHANLOAI");

            while (odr.Read())
            {

                ND.Add(new Noi_DungModels
                {
                    id = odr["id"].ToString(),
                    ID_BAIVIET = odr["ID_BAIVIET"].ToString(),
                    ID_PHANLOAI = odr["ID_PHANLOAI"].ToString(),
                    NOIDUNG = odr["NOIDUNG"].ToString(),
                    tenphanloai = odr["name"].ToString(),

                });
            }
            General.conn_global.Close();
            return ND;
        }
    }
}